package testExcel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class changData {
	public static void createExcel(Vector<Vector<String>> vector) {
		WritableWorkbook book = null;
		String absoluteUrl = "";
		try {
			String fileName = "E://bb.xls";// 声明文件名
			System.out.println(fileName);
			
			book = Workbook.createWorkbook(new File(fileName));// 创建excel文件
			File file = new File(fileName);
			if (!file.exists() || file.isDirectory()) {
				file.createNewFile();
			}
			// 创建工作表
			int totle = vector.size();// 获取vector集合的size
			System.out.println(totle);
			// int maxRownum = 60000;//
			// 每个工作表格最多存储60000条数据（注：excel表格一个工作表可以存储65536条）

			// int avg = totle / maxRownum;
			// for (int page = 1; page < avg + 1; page++) {
			int page = 1;
			WritableSheet sheet = book.createSheet("信息" + (page), page);

			WritableCellFormat form = new WritableCellFormat();
			form.setAlignment(Alignment.CENTRE);
			// merge the sheet cell;
			sheet.mergeCells(0, 0, 4, 0);
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置日期格式
			Label title0 = new Label(0, 0, "信息列表(" + df.format(new Date())
					+ ")", form);

			Label title = new Label(0, 1, "Source", form);
			Label title2 = new Label(1, 1, "Username", form);
			Label title3 = new Label(2, 1, "Company", form);
			Label title4 = new Label(3, 1, "Email", form);
			Label title5 = new Label(4, 1, "Phone", form);
			
			sheet.addCell(title0);
			sheet.addCell(title);
			sheet.addCell(title2);
			sheet.addCell(title3);
			sheet.addCell(title4);
			sheet.addCell(title5);
			int i = 2;
			Iterator<Vector<String>> it = vector.iterator();
			while (it.hasNext()) {
				Vector<String> v = it.next();
				Iterator<String> ite = v.iterator();
				int j = 0;
				while (ite.hasNext()) {
					Label label = new Label(j, i, ite.next());
					sheet.addCell(label);
					j++;
				}
				i++;
			}
			// }
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} finally {
			try {
				book.write();
				book.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}

		}
	}
	
	public static String changHttp(String content){
		if(content==null){
			return "";
		}
		int i = content.indexOf("from:");
		System.out.println(i);
		
		if(i>=0){
			String temp = content.substring(i+6);
			return temp;
		}else{
			return content;
		}
	}
	
	public static String changUserName(String content){
		if(content==null){
			return "";
		}
		int i = content.indexOf(":");
		if(i>=0){
			String temp = content.substring(i+2);
			return temp;
		}else{
			return content;
		}
	}
	
	public static String changCompany(String content){
		if(content==null){
			return "";
		}
		int i = content.indexOf(":");
		if(i>=0){
			String temp = content.substring(i+2);
			return temp;
		}else{
			return content;
		}
	}
	
	public static String changEmail(String content){
		if(content==null){
			return "";
		}
		int i = content.indexOf(":");
		if(i>=0){
			String temp = content.substring(i+2);
			return temp;
		}else{
			return content;
		}
	}
	
	public static String changPhone(String content){
		if(content!=null){
			int i = content.indexOf(":");
			if(i>=0){
				String temp = content.substring(i+2);
				return temp;
			}else{
				return content;
			}
		}else{
			return "";
		}
		
	}
	public static void main(String[] args) throws BiffException, IOException {
		
		List<People> list = new ArrayList<People>();
		File file = new File("E://aa.xls");
		InputStream in = new FileInputStream(file);
		
        Workbook workbook = Workbook.getWorkbook(in); // 创建工作薄
		Sheet sheet = workbook.getSheet(0); // 得到工作表
		int Columns = sheet.getColumns(); // 总列数
		int rsRows = sheet.getRows(); // 总行数
		System.out.println(rsRows);
		for(int i=1;i<rsRows;i++){
			String content = sheet.getCell(0, i).getContents().replaceAll(" ", "");;
			if(!"".equals(content)){
				//System.out.println(content);
				if(content.indexOf("Date")>=0){
					i++;
					People p = new People();
					for(int j=i;j<rsRows;j++){
						
						String temp = sheet.getCell(0, j).getContents().replaceAll(" ", "");
						if(temp.indexOf("Submitted")>=0){
							p.setSource(temp);
						}else if(temp.indexOf("username")>=0){
							p.setUsername(temp);
						}else if(temp.indexOf("email")>=0){
							p.setEmail(temp);
						}else if(temp.indexOf("company")>=0){
							p.setCompany(temp);
						}else if(temp.indexOf("phone")>=0){
							p.setPhone(temp);
						}else if(temp.indexOf("Date")>=0){
							break;
						}
					}
					list.add(p);
				}
			}
		}
		in.close();
		
		for(People a:list){
			System.out.println(changHttp(a.getSource()));
			System.out.println(changUserName(a.getUsername()));
			System.out.println(changEmail(a.getEmail()));
			System.out.println(changPhone(a.getPhone()));
			System.out.println(changCompany(a.getCompany()));
		}
		
		Vector<Vector<String>> vector = new Vector<Vector<String>>();
		Vector<String> v = null;
		for(People a:list){
			v = new Vector<String>();
			v.add("" + changHttp(a.getSource()));
			v.add("" + changUserName(a.getUsername()));
			v.add("" + changCompany(a.getCompany()));
			v.add("" + changEmail(a.getEmail()));
			v.add("" + changPhone(a.getPhone()));
			vector.add(v);
		}
		changData.createExcel(vector);
	}

}
